using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ETL.Entity;

namespace ETL.DataAccess
{
    public class ProjectRepository
    {
        public PROJECT GetProject(int? projectId)
        {
            if (projectId.HasValue)
            {
                var db = new ETLDataEntities();
                var result = from s in db.PROJECTS 
                             where s.STATUS != "DRAFT"
                             orderby s.PROJECT_ID select s;
                if (result != null)
                {
                    return result.First();
                }
                else
                {
                    return null;
                }
               
            }
            else return null;
        }

        //get all projects
        public IEnumerable<PROJECT> GetProjects()
        {
            var db = new ETLDataEntities();
            var result = from s in db.PROJECTS
                            orderby s.PROJECT_ID
                            select s;
            if (result != null)
            {
                return result;
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// Get projects by user
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IEnumerable<PROJECT> GetProjectsByUser(string userId)
        {
           //select prj.project_name
            //, tsk.task_name
            //, rin.resource_name
            //from projects prj,
            //tasks tsk,
            //scheduled_resource_usages sch,
            //resource_instances rin
            //where prj.project_id = tsk.gen_project_id
            //and tsk.gen_task_id = sch.gen_task_id
            //and sch.gen_resource_id = rin.gen_resource_id 
            
            var db = new ETLDataEntities();

            var result = from p in db.PROJECTS
                         join t in db.TASKS on p.PROJECT_ID equals t.GEN_PROJECT_ID
                         join s in db.SCHEDULED_RESOURCE_USAGES on t.GEN_TASK_ID equals s.GEN_TASK_ID
                         join r in db.RESOURCE_INSTANCES on s.GEN_RESOURCE_ID equals r.GEN_RESOURCE_ID     
                         where r.RESOURCE_NAME.ToLower().Equals(userId)
                         orderby p.PROJECT_ID
                         select p;

            return result.Distinct();

        }

        //get closed projects
        public IEnumerable<PROJECT> GetClosedProjects()
        {
            var db = new ETLDataEntities();
            var projects = from p in db.PROJECTS
                           where p.STATUS.Equals("CLOSED")
                           orderby p.PROJECT_ID descending
                           select p;
            if (projects != null)
            {
                return projects;
            }
            else
            {
                return null;
            }
        }

        //get open projects
        public IEnumerable<PROJECT> GetOpenProjects()
        {
            var db = new ETLDataEntities();
            var projects = from p in db.PROJECTS
                           where p.STATUS.Equals("OPEN")
                           orderby p.PROJECT_ID ascending
                           select p;
            if (projects != null)
            {
                return projects;
            }
            else
            {
                return null;
            }
        }
    }
}

